package cz.acies.genea.data.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;

import cz.acies.data.DBConnector;
import cz.acies.data.dao.DefaultDAO;
import cz.acies.data.dao.DefaultMapListHandler;
import cz.acies.data.dao.DefaultRowProcessor;
import cz.acies.data.persist.ModelObject;
import cz.acies.genea.data.persist.GedFamily;
import cz.acies.genea.data.persist.GedPerson;
import cz.acies.genea.data.persist.GedPersonBinding;
import cz.acies.genea.utils.GED;
import cz.acies.uniql.sql.InsertQuery;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UpdateQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLJoin;
import cz.acies.uniql.sql.element.UniQLWhere;
import cz.acies.utils.UIDGenerator;

public class GedPersonDAO extends DefaultDAO
{
	public GedPersonDAO() {
		this.tableName = "person";
	}
	
	public List<GedPerson> selectAll() {
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {  
			SelectQuery sql = new SelectQuery(dbmd())
			.addFrom("person")
			.addOrderBy("lastName", "ASC")
			.addOrderBy("birthDate", "ASC")
			;
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("id");
				GedPerson record = new GedPerson(id, param);
				records.add(record);
			}
			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}

	public GedPerson selectById(String id) {
		GedPerson record = null;
		try {  
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(new SelectQuery(dbmd())
				.addFrom("person").add(new UniQLCondition("person.id", "=", "'"+id+"'"))
				.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {
				record = new GedPerson(id, param);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return record;
	}

	public String insert(GedPerson record) {
		String id = record.getId();
		try {
			if (id == null || id.trim().length() == 0 || !id.startsWith("GE.R.")) {
				id = UIDGenerator.createUID("GE.R");
			}
			InsertQuery sql = new InsertQuery(dbmd()).into("person")
				.addParam("id", ""+id)
				.addParam("projectId", ""+((record.hasProjectId()) ? record.getProjectId() : ""))
				.addParam("firstName", ""+record.getFirstName())
				.addParam("lastName", ""+record.getLastName())
				.addParam("maidenName", ""+record.getMaidenName()) 
				.addParam("gender", ""+record.getGender())
				.addParam("birthDate", ""+record.getBirthDate())
				.addParam("birthPlace", ""+record.getBirthPlace());
			
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			qr.update(sql.toSQLString());
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return id;
	}

	public void update(GedPerson record) {
		if (record != null) {
			try {
				GedFamily parents = record.getParents();
				String birthDate = (record.hasBirthDate()) ? DateFormatUtils.format(record.getBirthDate(), "yyyy-MM-dd") : "2099-01-01";
				String deathDate = (record.hasDeathDate()) ? DateFormatUtils.format(record.getDeathDate(), "yyyy-MM-dd") : "2099-01-01";
				UpdateQuery sql = new UpdateQuery(dbmd())
				.addFrom("person")
					.addParam("projectId", ""+((record.hasProjectId()) ? record.getProjectId() : ""))
					.addParam("firstName", ""+record.getFirstName())
					.addParam("lastName", ""+record.getLastName())
					.addParam("maidenName", ""+record.getMaidenName()) 
					.addParam("gender", ""+record.getGender())
					.addParam("birthDate", ""+birthDate)
					.addParam("birthPlace", ""+record.getBirthPlace())	
					.addParam("deathDate", ""+deathDate)
					.addParam("deathPlace", ""+record.getDeathPlace())
					.addParam("parents", ((parents != null) ? parents.getId() : ""))
				.add(new UniQLCondition("id", "=", "'"+record.getId()+"'"));

				QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
				qr.update(sql.toSQLString());
			} catch (Exception exc) {
				exc.printStackTrace();
			}
		}
	}

	public void update(Map<String, Object> params) throws Exception {
		String DATE_PATTERN = "\\d{1,2}.\\d{1,2}.\\d{4}";
		GedPerson record = new GedPerson();
		for (String key : params.keySet()) {
			if (key.equals("id")) record.setId((String)params.get(key));
			if (key.equals("firstName")) record.setFirstName((String)params.get(key));
			if (key.equals("lastName")) record.setLastName((String)params.get(key));
			if (key.equals("maidenName")) record.setMaidenName((String)params.get(key));
			if (key.equals("gender")) record.setGender((String)params.get(key));
			
			if (key.equals("birthDate")) {
				try {
					String birthDate = (String)params.get(key);
					if (birthDate == null) birthDate = "2099-01-01";
					if (!birthDate.matches(DATE_PATTERN)) {
						birthDate = "2099-01-01";
					}
					birthDate = "" + new java.sql.Date(DateUtils.parseDate(birthDate, "dd.MM.yyyy").getTime());					
					record.setBirthDate(Timestamp.valueOf(birthDate + " 00:00:00"));
				} catch (Exception exc) {
					exc.printStackTrace();
				}
			}
			if (key.equals("birthPlace")) record.setBirthPlace((String)params.get(key));
			
			if (key.equals("deathDate")) {
				try {
					String deathDate = (String)params.get(key);
					if (deathDate == null) deathDate = "01.01.2099";
					if (!deathDate.matches(DATE_PATTERN)) {
						deathDate = "01.01.2099";
					}
					deathDate = "" + new java.sql.Date(DateUtils.parseDate(deathDate, "dd.MM.yyyy").getTime());
					record.setDeathDate(Timestamp.valueOf(deathDate + " 00:00:00"));
				} catch (Exception exc) {
					exc.printStackTrace();
				}
			}
			if (key.equals("deathPlace")) record.setDeathPlace((String)params.get(key));
			
			if (key.equals("fatherId")) {
				if (record.hasFather()) {
					GedPerson p = new GedPerson("");
					//p.setFirstName((String)params.get(key));
					record.setFather(p);
				} else {
					GedPerson p = new GedPerson((String)params.get(key));
					record.setFather(p);
					//record.getFather();
					//record.getFather().setFirstName((String)params.get(key));
				}				
			}
			/*
			if (key.equals("fatherName")) {
				if (record.hasFather()) {
					Person p = new Person("");
					p.setFirstName((String)params.get(key));
					record.setFather(p);
				} else {
					record.getFather().setFirstName((String)params.get(key));
				}
			}
			*/
			if (key.equals("motherId")) {
				if (record.hasMather()) {
					GedPerson p = new GedPerson("");
					//p.setFirstName((String)params.get(key));
					record.setMather(p);
				} else {
					GedPerson p = new GedPerson((String)params.get(key));
					record.setMather(p);
					//record.getMather().setFirstName((String)params.get(key));
				}				
			}			
			/*
			if (key.equals("matherName")) {
				if (record.hasMather()) {
					Person p = new Person("");
					p.setFirstName((String)params.get(key));
					record.setMather(p);
				} else {
					record.getMather().setFirstName((String)params.get(key));
				}
			}
			*/
			//descript
			//bookCode
		}
		String id = record.getId();
		//System.out.println("GedPersonDAO.update().id: " + id);
		if (existsRecord(id)) {
			this.update(record);
		} else {
			this.insert(record);
		}
	}

	/**
	 * Vyplní pro osobu nejbližší rodinu (rodiče, sourozence, rodiny s partnery a jejich děti)
	 * @param person
	 * @return
	 */
	public GedPerson selectImmediateFamily(GedPerson person) throws Exception {
		person = selectById(person.getId());
		GedFamilyDAO fDao = new GedFamilyDAO();
		// rodice
		GedFamily parents = person.getParents();
		if (!person.hasParents()) {
			parents = selectParents(person); 
			person.setParents(parents);
		}
		if (parents.hasMan() && parents.getMan().hasId()) {
			String id = parents.getMan().getId();
			GedPerson father = selectById(id); 
			parents.setMan(father);
		}
		if (parents.hasWoman() && parents.getWoman().hasId()) {
			String id = parents.getWoman().getId();
			GedPerson mother = selectById(id); 
			parents.setWoman(mother);
		}
		
		// sourozenci
		
		List<ModelObject> binds = selectByWhere("person_bindings AS pb", new UniQLWhere()
			.add(new UniQLCondition("pb.objectId", "=", "'"+parents.getId()+"'"), "AND")
			.add(new UniQLCondition("pb.bindingType", "=", "'"+GED.Type.CHILD+"'"), "AND")
			.add(new UniQLCondition("pb.extend", "=", "'"+GED.Extend.FAMILY+"'")), GedPersonBinding.class);
		
		for (ModelObject object : binds) {
			if (object instanceof GedPersonBinding) {
				GedPersonBinding bind = (GedPersonBinding) object;
				String id = bind.getConnectId();
				GedPerson sibling = selectById(id);
				parents.addChild(sibling);
			}
		}

		// rodiny s partnery
		List<GedPerson> partners = selectPartners(person);
		
		return person;
	}
	
	public GedFamily selectFamily(GedPerson person, GedPerson partner) {
		GedFamily family = null;
		try {
			GedPerson man = person;
			GedPerson woman = partner;
			if (!person.getGender().equals("M")) {
				man = partner;
				woman = person;
			}
			String manId = (man != null && man.hasId()) ? man.getId() : ""; 
			String womanId = (woman != null && woman.hasId()) ? woman.getId() : "";
			SelectQuery sql = new SelectQuery(dbmd())
			.addFrom("family AS f")
			.add(new UniQLCondition("f.manId", "=", "'" +manId + "'"), "AND")
			.add(new UniQLCondition("f.womanId", "=", "'" + womanId + "'"));

			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			if (!list.isEmpty()) {
				String id = (String) list.get(0).get("id");
				family = new GedFamily(man, woman);
				family.setId(id);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return family;
	}

	public GedFamily selectParents(GedPerson person) throws Exception {
		GedFamily parents = new GedFamily();

		UniQLWhere where = new UniQLWhere()
		.add(new UniQLCondition("person_bindings.objectId", "=", "'"+person.getId()+"'"), "AND")
		.add(new UniQLCondition("person_bindings.bindingType", "=", "'"+GED.Type.PARENT+"'"), "AND")
		.add(new UniQLCondition("person_bindings.extend", "=", "'"+GED.Extend.FAMILY+"'"));
		
		List<ModelObject> binds = selectByWhere("person_bindings", where, GedPersonBinding.class);
		for (ModelObject object : binds) {
			if (object instanceof GedPersonBinding) {
				GedPersonBinding bind = (GedPersonBinding) object;
				String id = bind.getConnectId();
				parents = new GedFamilyDAO().selectById(id);
			}
		}
		return parents;
	}

	public List<GedFamily> selectPartnersFamily(GedPerson person) throws Exception {
		List<GedFamily> records = new ArrayList<GedFamily>();
		try {
			String objectId = person.getId();
			
			SelectQuery sql = new SelectQuery(dbmd())
			.addField("f.*")
			.addFrom("person_bindings AS b")
			.add(new UniQLJoin().addFrom("family AS f").add(new UniQLCondition("f.id", "=", "b.connectId")))
			.add(new UniQLCondition("b.objectId", "=", "'"+objectId+"'"), "AND")
			.add(new UniQLCondition("b.bindingType", "=", "'"+GED.Type.PARTNER+"'"), "AND")
			.add(new UniQLCondition("b.extend", "=", "'"+GED.Extend.FAMILY+"'"));
			
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {
				String id = (String) param.get("ID");
				GedFamily record = new GedFamily(id, param);
				records.add(record);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}
	
	public List<GedPerson> selectPartners(GedPerson person) throws Exception {
		String objectId = person.getId();
		
		SelectQuery sql = new SelectQuery(dbmd())
		.addField("p.*")
		.addFrom("person_bindings AS b")
		.add(new UniQLJoin().addFrom("person AS p").add(new UniQLCondition("p.id", "=", "b.connectId")))
		.add(new UniQLCondition("b.objectId", "=", "'"+objectId+"'"), "AND")
		.add(new UniQLCondition("b.bindingType", "=", "'PARTNER'"), "AND")
		.add(new UniQLCondition("b.extend", "=", "'PERSON'"));
		
		List<GedPerson> records = new ArrayList<GedPerson>();
		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
		for (Map<String, Object> param : list) {				
			String id = (String) param.get("ID");
			GedPerson record = new GedPerson(id, param);
			records.add(record);
		}
		return records;
	}
	
	public List<GedPerson> selectPartners(String id) throws Exception {
		return selectPartners(new GedPerson(id));
	}
	
	public List<GedPerson> selectChildernById(GedPerson parent) {
		String parentId = parent.getId();
		List<GedPerson> childern = selectChildernById(parentId);
		parent.setChildern(childern);
		return childern;
	}
	
	public List<GedPerson> selectChildernById(String parentId) {
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {  
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			
			UniQLWhere where = new UniQLWhere();
			List<Map<String, Object>> fams = qr.query(new SelectQuery(dbmd())
					.addFrom("family")
					.add(new UniQLCondition("family.manId", "=", "'"+parentId+"'"), "OR")
					.add(new UniQLCondition("family.womanId", "=", "'"+parentId+"'"), "OR")
					.add(new UniQLCondition("family.id", "=", "'"+parentId+"'"))
				.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : fams) {
				String id = (String) param.get("id");
				if (id != null && id.startsWith("GE.F")) {
					where.add(new UniQLCondition("person.parents", "=", "'"+id+"'"), "OR");
				}
			}
			if (!where.isEmpty()) {
				List<Map<String, Object>> list = qr.query(new SelectQuery(dbmd())
				.addFrom("person").addWhere(where)//.addOrderBy("person.birthDate")
				.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
				for (Map<String, Object> param : list) {				
					String id = (String) param.get("ID");
					GedPerson record = new GedPerson(id, param);
					records.add(record);
				}
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}

	public List<GedPerson> selectChildernByFamily(GedFamily family) throws Exception {
		String objectId = family.getId();
		
		SelectQuery sql = new SelectQuery(dbmd())
		.addField("p.*")
		.addFrom("person_bindings AS b")
		.add(new UniQLJoin().addFrom("person AS p").add(new UniQLCondition("p.id", "=", "b.connectId")))
		.add(new UniQLCondition("b.objectId", "=", "'"+objectId+"'"), "AND")
		.add(new UniQLCondition("b.bindingType", "=", "'"+GED.Type.CHILD+"'"), "AND")
		.add(new UniQLCondition("b.extend", "=", "'"+GED.Extend.FAMILY+"'"));
		
		List<GedPerson> records = new ArrayList<GedPerson>();
		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
		for (Map<String, Object> param : list) {
			String id = (String) param.get("ID");
			GedPerson record = new GedPerson(id, param);
			records.add(record);
		}
		return records;
	}
	public List<GedPerson> selectByProjectId(String projectId) {
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {  
			SelectQuery sql = new SelectQuery(dbmd())
			.addField("p.*")
			.addFrom("person AS p")
			.add(new UniQLCondition("p.projectId", "=", "'" + projectId + "'"));

			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("ID");
				GedPerson record = new GedPerson(id, param);
				records.add(record);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}
	
	// -----  -----------------------------------------------------------------
	
	public boolean recordExists(String id) {
		try {
			String sql = "SELECT id FROM person WHERE person.id = '"+id+"'";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql, new DefaultMapListHandler(new DefaultRowProcessor()));
			return !list.isEmpty();
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return false;
	}
}
/*
	public List<GedPerson> selectAll() {
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {  
			String sql = "SELECT * FROM person";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			ResultSetHandler<List<GedPerson>> handler = new BeanListHandler<GedPerson>(GedPerson.class, 
				new BasicRowProcessor(new BeanProcessor() {
					@Override
					protected Object processColumn(ResultSet rs, int index, Class<?> propType) throws SQLException {
						//System.out.println("BeanProcessor.processColumn("+index+",  "+propType+")");
						if (propType == GedFamily.class) {
							String id = rs.getString(index);
							return new GedFamily(id);
						} else {
							return super.processColumn(rs, index, propType);
						}
					}
				}) 
			);
			records = qr.query(sql, handler);
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}

	public GedPerson selectById(String id) {
		GedPerson record = null;
		try {  
			String sql = "SELECT * FROM person WHERE person.ID = '"+id+"'";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			ResultSetHandler<List<GedPerson>> handler = new BeanListHandler<GedPerson>(GedPerson.class, new BasicRowProcessor(new GedPersonBeanProcessor()));
			List<GedPerson> records = qr.query(sql, handler);
			if (!records.isEmpty()) {
				record = records.get(0);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		//System.out.println("record: " + record);
		return record;
	}

	public String insert(GedPerson record) {
		String id = record.getId();
		try {
			if (id == null || id.trim().length() == 0 || !id.startsWith("GE.R.")) {
				id = UIDGenerator.createUID("GE.R");
			}
			InsertQuery sql = new InsertQuery(dbmd()).into("person")
				.addParam("id", ""+id)
				.addParam("projectId", ""+((record.hasProjectId()) ? record.getProjectId() : ""))
				.addParam("firstName", ""+record.getFirstName())
				.addParam("lastName", ""+record.getLastName())
				.addParam("maidenName", ""+record.getMaidenName()) 
				.addParam("gender", ""+record.getGender())
				.addParam("birthDate", ""+record.getBirthDate())
				.addParam("birthPlace", ""+record.getBirthPlace());
			
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			qr.update(sql.toSQLString());
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return id;
	}
	
	public void update(GedPerson record) {
		if (record != null) {
			try {
				String birthDate = (record.hasBirthDate()) ? DateFormatUtils.format(record.getBirthDate(), "yyyy-MM-dd") : "2099-01-01";
				UpdateQuery sql = new UpdateQuery(dbmd())
				.addFrom("person")
					.addParam("projectId", ""+((record.hasProjectId()) ? record.getProjectId() : ""))
					.addParam("firstName", ""+record.getFirstName())
					.addParam("lastName", ""+record.getLastName())
					.addParam("maidenName", ""+record.getMaidenName()) 
					.addParam("gender", ""+record.getGender())
					.addParam("birthDate", ""+birthDate)
					.addParam("birthPlace", ""+record.getBirthPlace())
				.add(new UniQLCondition("id", "=", "'"+record.getId()+"'"));

				QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
				qr.update(sql.toSQLString());
			} catch (Exception exc) {
				exc.printStackTrace();
			}
		}
	}
	
	public void update(Map<String, Object> params) {
		
		//String DATE_PATTERN = "\\d{4}-\\d{1,2}-\\d{1,2}";
		String DATE_PATTERN = "\\d{1,2}.\\d{1,2}.\\d{4}";
			//descript
			//bookCode
	}
	
	public boolean recordExists(String id) {
		try {
			String sql = "SELECT ID FROM person WHERE person.ID = '"+id+"'";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql, new DefaultMapListHandler(new DefaultRowProcessor()));
			return !list.isEmpty();
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return false;
	}
	
	public List<GedPerson> selectChildernById(GedPerson parent) {
		String parentId = parent.getId();
		List<GedPerson> childern = selectChildernById(parentId);
		parent.setChildern(childern);
		return childern;
	}
	
	public List<GedPerson> selectChildernById(String parentId) {
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {  
			String sql = 
			"SELECT * FROM person AS p " + 
			"WHERE p.FATHER_ID = '"+parentId+"' OR p.MATHER_ID = '"+parentId+"' " +
			" ORDER BY p.BIRTH_DATE";

			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql, new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("ID");
				GedPerson record = new GedPerson(id, param);
				records.add(record);
			}
			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}

*/